﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.DBM;

namespace KPIS.GERP.GHRMS.BLL
{
    public class RecruitmentBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void insert(int cmd_seq, int tmp_emp_seq, int move_seq, int tmp_edu_seq, string old_pos_name
                , int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary
                , double salary, string pos_name, string pos_ref_no, int class_seq, int job_site_seq
                , string job_site_name, string start_date, int pos_type_seq, string remarks, int created_by)
        {
            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(21);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);
                dbManager.AddParameters(9, "@salary", salary);
                dbManager.AddParameters(10, "@pos_name", pos_name);
                dbManager.AddParameters(11, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(12, "@class_seq", class_seq);
                dbManager.AddParameters(13, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(14, "@job_site_name", job_site_name);
                dbManager.AddParameters(15, "@start_date", start_date);
                dbManager.AddParameters(16, "@remarks", remarks);
                dbManager.AddParameters(17, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(18, "@created_by", created_by);
                dbManager.AddParameters(19, "@created_when", DateTime.Today);
                dbManager.AddParameters(20, "@pos_type_seq", pos_type_seq);

                string sql;

                sql = " insert into hrm_ps_command_add(cmd_seq,tmp_emp_seq,move_seq,tmp_edu_seq,old_pos_name,"
    + " old_pos_type_seq ,old_class_seq ,old_job_site_name,old_salary ,salary,"
    + " pos_name ,pos_ref_no,class_seq,job_site_seq ,job_site_name,"
    + " start_date,remarks,record_status,pos_type_seq,created_by,created_when)"


    + " values(@cmd_seq,@tmp_emp_seq,@move_seq,@tmp_edu_seq,@old_pos_name,"
    + " @old_pos_type_seq ,@old_class_seq ,@old_job_site_name,@old_salary ,@salary,"
    + " @pos_name ,@pos_ref_no,@class_seq,@job_site_seq ,@job_site_name,"
    + " @start_date,@remarks,@record_status,@pos_type_seq,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        public void InsertTransfer(int cmd_seq, int tmp_emp_seq, int move_seq, int tmp_edu_seq
                , string old_pos_name, int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary
                , string pos_name, string pos_ref_no, int pos_type_seq, int class_seq, int job_site_seq, string job_site_name
                , double salary
                , string ent_no, string ent_pos_name, int ent_pos_type_seq, int ent_class_seq, string ent_job_site_name
                , DateTime ent_date, DateTime start_date, string remarks, int created_by)
        {
            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(27);

                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@tmp_emp_seq", tmp_emp_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);

                dbManager.AddParameters(9, "@ent_no", ent_no);
                dbManager.AddParameters(10, "@ent_pos_name", ent_pos_name);
                dbManager.AddParameters(11, "@ent_pos_type_seq", ent_pos_type_seq);
                dbManager.AddParameters(12, "@ent_class_seq", ent_class_seq);
                dbManager.AddParameters(13, "@ent_job_site_name", ent_job_site_name);
                dbManager.AddParameters(14, "@ent_date", ent_date);

                dbManager.AddParameters(15, "@salary", salary);
                dbManager.AddParameters(16, "@pos_name", pos_name);
                dbManager.AddParameters(17, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(18, "@class_seq", class_seq);
                dbManager.AddParameters(19, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(20, "@job_site_name", job_site_name);
                dbManager.AddParameters(21, "@start_date", start_date);
                dbManager.AddParameters(22, "@remarks", remarks);
                dbManager.AddParameters(23, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(24, "@created_by", created_by);
                dbManager.AddParameters(25, "@created_when", DateTime.Today);
                dbManager.AddParameters(26, "@pos_type_seq", pos_type_seq);

                string sql;

                sql = " insert into hrm_ps_command_add(cmd_seq,tmp_emp_seq,move_seq,tmp_edu_seq,"
                    + " old_pos_name,old_pos_type_seq ,old_class_seq ,old_job_site_name,old_salary ,"
                    + " ent_no, ent_pos_name, ent_pos_type_seq, ent_class_seq, ent_job_site_name, ent_date,"
                    + " salary,pos_name ,pos_ref_no,class_seq,job_site_seq ,job_site_name,"
                    + " start_date,remarks,record_status,pos_type_seq,created_by,created_when)"


                    + " values(@cmd_seq,@tmp_emp_seq,@move_seq,@tmp_edu_seq,"
                    + " @old_pos_name, @old_pos_type_seq, @old_class_seq ,@old_job_site_name,@old_salary ,"
                    + " @ent_no, @ent_pos_name, @ent_pos_type_seq, @ent_class_seq, @ent_job_site_name, @ent_date,"
                    + " @salary,@pos_name ,@pos_ref_no,@class_seq,@job_site_seq ,@job_site_name,"
                    + " @start_date,@remarks,@record_status,@pos_type_seq,@created_by,@created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        public void Update(int cmd_add_seq, int cmd_seq, int tmp_emp_seq, int move_seq, int tmp_edu_seq
            , string old_pos_name, int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary
            , string pos_name, string pos_ref_no, int class_seq, int job_site_seq, string job_site_name, double salary
            , string start_date, int pos_type_seq, string remarks, int updated_by)
        {

            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(19);
                dbManager.AddParameters(0, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(1, "@cmd_add_seq", cmd_add_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);
                dbManager.AddParameters(9, "@salary", salary);
                dbManager.AddParameters(10, "@pos_name", pos_name);
                dbManager.AddParameters(11, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(12, "@class_seq", class_seq);
                dbManager.AddParameters(13, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(14, "@job_site_name", job_site_name);
                dbManager.AddParameters(15, "@start_date", Convert.ToDateTime(start_date));
                dbManager.AddParameters(16, "@remarks", remarks);
                dbManager.AddParameters(17, "@updated_by", updated_by);
                dbManager.AddParameters(18, "@updated_when", DateTime.Today);


                string sql;

                sql = " update  hrm_ps_command_add set"
                + " move_seq=@move_seq,"
                + " tmp_edu_seq=@tmp_edu_seq,"
                + " old_pos_name=@old_pos_name,"
                + " old_pos_type_seq=@old_pos_type_seq,"
                + " old_class_seq=@old_class_seq,"
                + " old_job_site_name=@old_job_site_name,"
                + " old_salary=@old_salary,"
                + " salary=@salary,"
                + " pos_name=@pos_name,"
                + " pos_ref_no=@pos_ref_no,"
                + " class_seq=@class_seq,"
                + " job_site_seq=@job_site_seq,"
                + " job_site_name=@job_site_name,"
                + " start_date=@start_date,"
                + " remarks=@remarks,"
                + " pos_type_seq=@pos_type_seq,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"
                + " where cmd_add_seq=@cmd_add_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        public void UpdateTransfer(int cmd_add_seq, int cmd_seq, int tmp_emp_seq, int move_seq, int tmp_edu_seq
            , string old_pos_name, int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary
            , string pos_name, string pos_ref_no, int pos_type_seq, int class_seq, int job_site_seq, string job_site_name, double salary
            , string ent_no, string ent_pos_name, int ent_pos_type_seq, int ent_class_seq, string ent_job_site_name
            , DateTime ent_date, DateTime start_date, string remarks, int updated_by)
        {

            if (chkDup(cmd_seq, tmp_emp_seq))
                return;
            if (chkSalary(class_seq, salary))
                return;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(25);
                dbManager.AddParameters(0, "@pos_type_seq", pos_type_seq);
                dbManager.AddParameters(1, "@cmd_add_seq", cmd_add_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@tmp_edu_seq", tmp_edu_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);

                dbManager.AddParameters(9, "@ent_no", ent_no);
                dbManager.AddParameters(10, "@ent_pos_name", ent_pos_name);
                dbManager.AddParameters(11, "@ent_pos_type_seq", ent_pos_type_seq);
                dbManager.AddParameters(12, "@ent_class_seq", ent_class_seq);
                dbManager.AddParameters(13, "@ent_job_site_name", ent_job_site_name);
                dbManager.AddParameters(14, "@ent_date", ent_date);

                dbManager.AddParameters(15, "@salary", salary);
                dbManager.AddParameters(16, "@pos_name", pos_name);
                dbManager.AddParameters(17, "@pos_ref_no", pos_ref_no);
                dbManager.AddParameters(18, "@class_seq", class_seq);
                dbManager.AddParameters(19, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(20, "@job_site_name", job_site_name);
                dbManager.AddParameters(21, "@start_date", Convert.ToDateTime(start_date));
                dbManager.AddParameters(22, "@remarks", remarks);
                dbManager.AddParameters(23, "@updated_by", updated_by);
                dbManager.AddParameters(24, "@updated_when", DateTime.Today);

                string sql;

                sql = " update hrm_ps_command_add set"
                + " move_seq=@move_seq,"
                + " tmp_edu_seq=@tmp_edu_seq,"
                + " old_pos_name=@old_pos_name,"
                + " old_pos_type_seq=@old_pos_type_seq,"
                + " old_class_seq=@old_class_seq,"
                + " old_job_site_name=@old_job_site_name,"
                + " old_salary=@old_salary,"
                + " ent_no = @ent_no,"
                + " ent_pos_name = @ent_pos_name,"
                + " ent_pos_type_seq = @ent_pos_type_seq,"
                + " ent_class_seq = @ent_class_seq,"
                + " ent_job_site_name = @ent_job_site_name,"
                + " ent_date = @ent_date,"
                + " salary=@salary,"
                + " pos_name=@pos_name,"
                + " pos_ref_no=@pos_ref_no,"
                + " class_seq=@class_seq,"
                + " job_site_seq=@job_site_seq,"
                + " job_site_name=@job_site_name,"
                + " start_date=@start_date,"
                + " remarks=@remarks,"
                + " pos_type_seq=@pos_type_seq,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when"
                + " where cmd_add_seq=@cmd_add_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }
        private Boolean chkDup(int cmd_seq, int tmp_emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "select * from hrm_ps_command_add"
                    + " where cmd_seq = " + cmd_seq
                    + " and tmp_emp_seq = " + tmp_emp_seq
                    + " and record_status='" + SystemInfo._ActiveRecordStatus + "'";
                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }

        private Boolean chkSalary(int class_seq, double salary)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@class_seq", class_seq);
                dbManager.AddParameters(2, "@salary", salary);

                string sql;
                sql = "select * "
                    + " from hrm_pr_salary_group_dtls d"
                    + " inner join hrm_pr_salary_groups g on d.sal_grp_seq = g.sal_grp_seq"
                    + " where g.class_seq = @class_seq"
                    + " and (d.sal_min > @salary or d.sal_max < @salary)"
                    + " and d.record_status = @ActiveRecord";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                if (dt.Rows.Count > 1)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (Exception ex)
            {
                throw (new Exception(ex.Message));
            }

            finally
            {
                dbManager.Dispose();
            }


        }
    }
}
